﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace DAL
{
    public class NavDAL
    {
        SqlConnection str = new SqlConnection("Data Source=PC201609040948;Initial Catalog=TianTianDai_Db;Persist Security Info=True;User ID=sa;pwd=123456");

        /// <summary>
        /// 显示导航
        /// </summary>
        /// <returns></returns>
        public List<Nav> ShowNav()
        {
            str.Open();
            string sql = "select * from Nav";
            SqlDataAdapter adap = new SqlDataAdapter(sql,str);
            DataTable ta = new DataTable();
            adap.Fill(ta);
            str.Close();
            List<Nav> listNav = new List<Nav>();
            foreach (DataRow item in ta.Rows)
            {
                Nav n = new Nav();
                n.NavId = Convert.ToInt32(item["NavId"]);
                n.NavName = item["NavName"].ToString();
                n.Navurl = item["Navurl"].ToString();
                listNav.Add(n);
            }
            return listNav;
            
        }
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteNav(int id)
        {
            str.Open();
            string sql = "delete Nav where NavId='" + id + "'";
            SqlCommand com = new SqlCommand(sql, str);
            var i = com.ExecuteNonQuery();
            return i;
        }
        /// <summary>
        /// 添加信息
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public int Nav_Add(Model.Nav p)
        {
            str.Open();
            string sql = "insert into Nav values('" + p.NavName + "','" + p.Navurl + "')";
            SqlCommand com = new SqlCommand(sql, str);
            var i = com.ExecuteNonQuery();
            return i;
        }
        /// <summary>
        /// 显示详细信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Nav ShowNavX(int id)
        {
            str.Open();
            string sql = "select * from Nav where NavId='" + id + "'";
            SqlDataAdapter adap = new SqlDataAdapter(sql, str);
            DataTable ta = new DataTable();
            adap.Fill(ta);
            str.Close();
            List<Nav> listNav = new List<Nav>();
            foreach (DataRow item in ta.Rows)
            {
                Nav j = new Nav();
                j.NavId = Convert.ToInt32(item["NavId"]);
                j.NavName = item["NavName"].ToString();
                j.Navurl = item["Navurl"].ToString();
                listNav.Add(j);
            }
            return listNav.FirstOrDefault();
        }
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int UpdateNav(Nav data)
        {
            str.Open();
            string sql = "update Nav set  NavName='" + data.NavName + "',Navurl='" + data.Navurl + "' where NavId='" + data.NavId + "'";
            SqlCommand com = new SqlCommand(sql, str);
            int i = com.ExecuteNonQuery();
            return i;
        }
        /// <summary>
        /// 搜索
        /// </summary>
        /// <param name="pp"></param>
        /// <returns></returns>
        public List<Nav> SeleteNav(string name)
        {
            str.Open();
            string sql = "selete NavId,NavName,Navurl from Nav where NavName like '" + name + "'";
            SqlDataAdapter adap = new SqlDataAdapter(sql, str);
            DataTable ta = new DataTable();
            adap.Fill(ta);
            List<Nav> listNav = new List<Nav>();
            foreach (DataRow item in ta.Rows)
            {
                Nav d = new Nav();
                d.NavId = Convert.ToInt32(item["NavId"]);
                d.NavName = item["NavName"].ToString();
                d.Navurl = item["Navurl"].ToString();
                listNav.Add(d);
            }
            return listNav;
        }
    }
}
